package com.jiuqi.abc.model.service;

import com.jiuqi.abc.ABCException;
import com.jiuqi.abc.intf.ABCIntfException;
import com.jiuqi.abc.intf.provider.member.IMemberProvider;
import com.jiuqi.abc.intf.provider.member.Member;
import com.jiuqi.abc.intf.provider.scheme.ISchemeProvider;
import com.jiuqi.abc.intf.provider.scheme.SchemeBean;
import com.jiuqi.abc.intf.provider.table.Field;
import com.jiuqi.abc.intf.provider.table.ITableProvider;
import com.jiuqi.abc.intf.provider.table.TableObj;
import com.jiuqi.abc.model.bean.ABCBean;
import com.jiuqi.abc.model.bean.ResourceMemberValue;
import com.jiuqi.abc.model.dao.ABCResourceMemberDao;
import com.jiuqi.abc.model.vo.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * 成本池类型维度成员信息service
 * 维度项编码 和 公式的对照关系
 *
 * @author 龚泽楷
 * @date 2023年04月24日 16:16
 */
@Service
public class ABCResourceMemberService {

    @Autowired
    private ABCResourceMemberDao abcResourceMemberDao;

    @Autowired
    private ABCResourceMemberService resourceMemberService;

    @Autowired
    private ABCDimensionService abcDimensionService;

    @Autowired
    private ABCModelService abcModelService;

    @Autowired
    private ABCBeanService abcBeanService;

    @Autowired
    private ISchemeProvider schemeProvider;

    @Autowired
    private ITableProvider tableProvider;

    @Autowired
    private IMemberProvider memberProvider;

    Logger logger = LoggerFactory.getLogger(ABCResourceMemberService.class);

    /**
     * 保存成本池维度项信息
     *
     * @param resourceMemberValues 前端传来的需要保存的  维度项公式信息（对应关系）
     * @param modelId              模型ID
     */
    @Transactional(rollbackFor = Exception.class)
    public void saveResourceMember(List<ResourceMemberValue> resourceMemberValues, String modelId) {
        //根据 模型ID 获取 成本池维度对象
        DimBeanVo dimBeanVo = getResourceDimBean(modelId);
        if (dimBeanVo != null) {
            for (ResourceMemberValue resourceMemberValue : resourceMemberValues) {
                resourceMemberValue.setModelId(modelId);
                resourceMemberValue.setDimId(dimBeanVo.getGuid());
                resourceMemberValue.setDimName(dimBeanVo.getName());
            }

            //删除原有信息
            resourceMemberService.deleteResourceMemberByModelId(modelId);

            //添加新增信息
            abcResourceMemberDao.batchSaveResourceMembers(resourceMemberValues);

            //更新abc模型的修改时间
            abcBeanService.updateModifyTime(modelId);
        }
    }

    /**
     * 将 旧模型guid 对应模型中 的 成本池类型维度成员信息 复制到 新模型guid 对应模型中
     * @param oldAbcModelGuid 旧模型guid
     * @param newAbcModelGuid 新模型guid
     */
    public void saveAsResourceMember(String oldAbcModelGuid,String newAbcModelGuid) {
        //根据 旧模型guid 获取 旧成本池维度项信息集合
        List<ResourceMemberValue> resourceMemberValueList = abcResourceMemberDao.getResourceMemberValueList(oldAbcModelGuid);
        //将 旧成本池维度项信息集合 中的 模型ID 设置为 新模型ID
        for (ResourceMemberValue resourceMemberValue : resourceMemberValueList) {
            resourceMemberValue.setModelId(newAbcModelGuid);
        }
        //将 处理后的 成本池维度项信息集合 持久化
        abcResourceMemberDao.batchSaveResourceMembers(resourceMemberValueList);
    }

    /**
     * 处理 前端发送的excel文件 并 进行持久化
     *
     * @param modelId 模型ID
     * @param file    前端发送的excel文件
     * @return 处理是否成功
     */
    @Transactional(rollbackFor = Exception.class)
    public void processImportData(String modelId, MultipartFile file) throws ABCException {
        //【获取excel数据】
        if (file.isEmpty()) {
            logger.error("文件不能为空");
            return;
        }

        //由输入流获取excel的实例
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(file.getInputStream());
        } catch (IOException e) {
            logger.error("处理前端发送的excel文件异常", e);
        }

        //获取第一个页签
        assert wb != null : "excel实例不能为空";
        XSSFSheet sheet = wb.getSheetAt(0);

        //成本池维度下维成员与取数公式关联关系对象集合
        List<ResourceMemberValue> resourceMemberValues = new ArrayList<>();

        //循环sesheet页中数据从第二行开始，第一行是标题
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
            //成本池维度下维成员与取数公式关联关系对象集合
            ResourceMemberValue resourceMemberValue = new ResourceMemberValue();
            //获取每一行数据,先获取一个行，之后获取这一个行中的所有列
            XSSFRow row = sheet.getRow(i);
            for (int idx = 0; idx < row.getLastCellNum(); idx++) {
                Cell cell = row.getCell(idx);
                if (cell == null) {
                    continue;
                }
                DataFormatter formatter = new DataFormatter();
                String cellValue = formatter.formatCellValue(cell).trim();
                processCellValue(resourceMemberValue, idx, cellValue);
            }
            resourceMemberValues.add(resourceMemberValue);
        }

        //导入数据中成本池编码 与 基础数据中成本池编码 进行比较，不能一一对应则校验不通过
        DimBeanVo dimBeanVo = getResourceDimBean(modelId);
        if (dimBeanVo != null) {
            //获取时期参数
            ABCHomeVo abcHomeVo = abcModelService.queryPeriodAndDW(modelId);
            List<String> periodBeanList = abcHomeVo.getPeriodBeanList();
            String period = periodBeanList.get(periodBeanList.size() - 1);

            //从基础数据中 获取 当前模型 配置的 成本池下 所有的维成员
            List<Member> members = memberProvider.get(dimBeanVo.getName(), period);
            boolean flag = true;
            for (int i = 0; i < members.size(); i++) {
                if (!members.get(i).getCode().equals(resourceMemberValues.get(i).getMemberCode())) {
                    flag = false;
                    break;
                }
            }
            if (!flag) {
                throw new ABCException("导入文件中成本池编码与数据库中成本池编码不对应，导入失败");
            }

            //【持久化 成本池维度下维成员与取数公式关联关系】
            resourceMemberService.saveResourceMember(resourceMemberValues, modelId);
        } else {
            throw new ABCException("基础数据中当前模型未配置成本池，导入失败");
        }
    }

    /**
     * 处理导入文件单元格数据
     * @param resourceMemberValue 成本池维度下维成员与取数公式关联关系对象
     * @param idx                 行号
     * @param cellValue           单元格数据
     */
    private void processCellValue(ResourceMemberValue resourceMemberValue, int idx, String cellValue) {
        //设置成本池维度下维度项编码
        if (idx == 0) {
            //去除编码前用于 展示 所加的 空格
            String trimmedMemberCode = cellValue.trim();
            resourceMemberValue.setMemberCode(trimmedMemberCode);

            //设置成本池维度下维度项对应取数公式
        } else if (idx == 2) {
            resourceMemberValue.setFormula(cellValue);
        }
    }


    /**
     * 根据 所属模型ID 获取 成本池维度项信息(女娲报表数据与数据库存储的公式进行匹配)（树形结构）
     *
     * @param modelId 所属模型ID
     * @return
     */
    public List<DimensionMemberVo> getResourceMemberTreeList(String modelId) {
        //根据 模型ID 获取 成本池维度对象
        DimBeanVo dimBeanVo = getResourceDimBean(modelId);
        if (dimBeanVo == null) return Collections.emptyList();
        //获取成本池维度名称
        String dimName = dimBeanVo.getName();

        //构建请求参数
        ABCHomeVo abcHomeVo = abcModelService.queryPeriodAndDW(modelId);
        List<String> periodBeanList = abcHomeVo.getPeriodBeanList();
        String period = periodBeanList.get(periodBeanList.size() - 1);
        DimensionParameterVo dimensionParameter = new DimensionParameterVo(modelId, period, dimName);

        //从女娲报表获取 维度项
        List<DimensionMemberVo> dimensionMemberList = abcDimensionService.getDimensionMemberListByVo(dimensionParameter);

        //由模型ID 和 成本池维度编码  从数据库获取维度项公式信息
        Map<String, String> resourceMemberMap = abcResourceMemberDao.getResourceMemberMap(modelId, dimName);

        //维度项 与 公式信息 进行匹配
        recursiveMatchFormula(dimensionMemberList, resourceMemberMap);

        return dimensionMemberList;
    }

    /**
     * 根据 模型ID 获取 成本池维度对象
     * @param modelId 模型ID
     * @return 成本池维度对象
     */
    private DimBeanVo getResourceDimBean(String modelId) {
        List<DimBeanVo> dimBeanVos = abcModelService.queryModelDimByModelGuidAndType(modelId, 1);
        if (dimBeanVos.isEmpty()){
            return null;
        }
        return dimBeanVos.get(0);
    }

    /**
     * 根据 所属模型ID 获取 成本池维度项信息(女娲报表数据与数据库存储的公式进行匹配)(非树形结构)
     *
     * @param modelGuid 所属模型ID
     * @return 成本池维度项信息
     */
    public List<ResourceMemberValue> getResourceMemberList(String modelGuid) {
        List<ResourceMemberValue> resourceMemberValueList = abcResourceMemberDao.getResourceMemberValueList(modelGuid);
        for (ResourceMemberValue memberValue : resourceMemberValueList) {
            try {
                //翻译
                ABCHomeVo abcHomeVo = abcModelService.queryPeriodAndDW(modelGuid);
                List<String> periodBeanList = abcHomeVo.getPeriodBeanList();
                String period = periodBeanList.get(periodBeanList.size() - 1);

                Member member = memberProvider.get(memberValue.getDimName(), memberValue.getMemberCode(), period);
                if (null != member) {
                    memberValue.setMemberTitle(member.getName());
                } else {
                    memberValue.setMemberTitle(memberValue.getMemberCode());
                }
            } catch (Exception e) {
                logger.error("获取维度项title异常", e);
            }
        }
        return resourceMemberValueList;
    }

    /**
     * 根据 模型ID 获取 数据方案，以此作为树形根节点
     * @param modelId 模型ID
     * @return 树形根节点
     */
    public TreeNodeVo getRootNode(String modelId) {
        //获取模型
        ABCBean abcBean = abcBeanService.getAbcBean(modelId);
        //获取模型绑定数据方案ID
        String dataSchemaId = abcBean.getDataSchemeID();

        //获取数据方案
        SchemeBean schemeBean = null;
        try {
            schemeBean = schemeProvider.getBean(dataSchemaId);
        } catch (ABCIntfException e) {
            logger.error("获取数据方案异常", e);
        }

        //创建树形对象
        assert schemeBean != null;
        TreeNodeVo rootNodeVo = new TreeNodeVo(schemeBean.getGuid(), schemeBean.getTitle(), schemeBean.getFolderGuid(), false);
        rootNodeVo.setDisabled(true);

        return rootNodeVo;
    }


    /**
     * 根据 模型ID 和 目录ID 获取取数表树形
     *
     * @param modelId  模型ID
     * @param folderId 目录ID
     * @return 获取取数表树形
     */
    public List<TableObj> getDataTableList(String modelId, String folderId) {
        ABCBean abcBean = abcBeanService.getAbcBean(modelId);
        String dataSchemaId = abcBean.getDataSchemeID();

        List<TableObj> dataTableList = new ArrayList<>();
        try {
            dataTableList = tableProvider.getChildren(dataSchemaId, "null".equals(folderId) ? "" : folderId);
        } catch (ABCIntfException e) {
            logger.error("获取取数表树形异常", e);
        }

        return dataTableList;
    }

    /**
     * 根据 具体的取数表名（英文） 获取 该表中所有字段信息
     *
     * @param tableName 具体的取数表名（英文）
     * @return 该表中所有字段信息
     */
    public List<Field> getTableFieldList(String tableName) {
        List<Field> fieldList = new ArrayList<>();
        try {
            fieldList = tableProvider.getFields(tableName);
        } catch (ABCIntfException e) {
            logger.error("获取取数表字段信息异常", e);
        }
        return fieldList;
    }

    /**
     * 根据 所属模型ID 和 维度编码dimName  获取 成本池维度项信息 [非本模块使用]
     *
     * @param modelId 所属模型ID
     * @param dimName 维度编码dimName
     * @return 成本池维度项信息
     */
    public Map<String, String> getResourceMemberMap(String modelId, String dimName) {
        return abcResourceMemberDao.getResourceMemberMap(modelId, dimName);
    }

    /**
     * 根据前端 模型ID 和 时期参数 获取 维度项信息集合 后 处理成excel文件 发送给前端 进行下载
     *
     * @param modelId     模型ID
     * @param response    返回响应
     * @throws IOException IO异常
     */
    public void processExportData(String modelId, HttpServletResponse response) {
        //①根据前端参数获取 前端当前显示的内容
        List<DimensionMemberVo> resourceMemberList = getResourceMemberTreeList(modelId);


        //②将数据组装成excel格式
        //创建行样式
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        //获取设置的语言
        //设置Excel表头
        String[] cellText = {"成本池编码", "成本池名称", "提取公式"};

        //创建sheet
        XSSFSheet sheet = createSheet(workbook, 0, cellText);

        //设置行宽
        sheet.setDefaultColumnWidth(44);

        //设置行高
        sheet.setDefaultRowHeight((short) 400);

        //开始处理数据
        recursiveSetData(resourceMemberList, sheet, cellStyle);


        //③数据组装成功，向前端发送
        ServletOutputStream stream = null;
        try {


            response.setHeader("Content-Disposition", "attachment; filename=myfile.xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            stream = response.getOutputStream();
            workbook.write(stream);
            stream.flush();

            stream.close();
            workbook.close();


        } catch (IOException e) {
            logger.error("动因数据导出异常", e);
        }


    }

    /**
     * 根据 所属模型ID 批量删除 多个成本池维度项信息
     * @param modelId 所属模型ID
     */
    @Transactional(rollbackFor = Exception.class)
    public void deleteResourceMemberByModelId(String modelId) {
        abcResourceMemberDao.deleteResourceMemberByModelId(modelId);

        //更新abc模型的修改时间
        abcBeanService.updateModifyTime(modelId);
    }

    /**
     * 公式匹配
     *
     * @param resourceMemberList 女娲报表获取的成本池维度项信息
     * @param resourceMemberMap  数据库中存储的成本池维度项公式信息
     */
    private void recursiveMatchFormula(List<DimensionMemberVo> resourceMemberList, Map<String, String> resourceMemberMap) {
        for (int i = 0; i < resourceMemberList.size(); i++) {
            DimensionMemberVo dimensionMemberVo = resourceMemberList.get(i);
            if (dimensionMemberVo.getSubMemberResourceList() == null) {
                String code = dimensionMemberVo.getCode();
                String formula = resourceMemberMap.get(code);
                dimensionMemberVo.setFormula(formula);
            } else {
                String code = dimensionMemberVo.getCode();
                String formula = resourceMemberMap.get(code);
                dimensionMemberVo.setFormula(formula);
                recursiveMatchFormula(dimensionMemberVo.getSubMemberResourceList(), resourceMemberMap);
            }
        }
    }

    /**
     * 为单元格赋值
     *
     * @param resourceMemberList 维度项信息
     * @param sheet              表格一个页签
     * @param cellStyle          样式
     */
    private void recursiveSetData(List<DimensionMemberVo> resourceMemberList, XSSFSheet sheet, XSSFCellStyle cellStyle) {
        for (int i = 0; i < resourceMemberList.size(); i++) {
            if (resourceMemberList.get(i).getSubMemberResourceList() == null) {
                DimensionMemberVo dimensionMemberVo = resourceMemberList.get(i);
                createRow(sheet, cellStyle, dimensionMemberVo);
            } else {
                DimensionMemberVo dimensionMemberVo = resourceMemberList.get(i);
                createRow(sheet, cellStyle, dimensionMemberVo);
                recursiveSetData(resourceMemberList.get(i).getSubMemberResourceList(), sheet, cellStyle);
            }
        }
    }


    /**
     * 创建 sheet
     *
     * @param workbook Excel表
     * @param rownum   行号
     * @param text     文本
     * @return XSSFSheet 返回sheet
     */
    private XSSFSheet createSheet(XSSFWorkbook workbook, int rownum, String[] text) {
        XSSFCellStyle headstyle = workbook.createCellStyle();
        headstyle.setAlignment(HorizontalAlignment.LEFT);

        XSSFSheet sheet = workbook.createSheet(String.format("第%s页", rownum + 1));
        XSSFRow title = sheet.createRow(0);

        for (int i = 0; i < text.length; i++) {
            createCell(title, headstyle, i, text[i], 0);
        }
        return sheet;
    }

    /**
     * 创建行并填充记录
     *
     * @param sheet     表格sheet
     * @param cellStyle 单元格样式
     */
    private void createRow(XSSFSheet sheet, XSSFCellStyle cellStyle, DimensionMemberVo dimensionMemberVo) {
        XSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
        for (int i = 0; i < 3; i++) {
            switch (i) {
                case 0:
                    createCell(row, cellStyle, i, dimensionMemberVo.getCode(), dimensionMemberVo.getTableLevel());
                    break;
                case 1:
                    createCell(row, cellStyle, i, dimensionMemberVo.getName(), dimensionMemberVo.getTableLevel());
                    break;
                case 2:
                    createCell(row, cellStyle, i, dimensionMemberVo.getFormula(), dimensionMemberVo.getTableLevel());
                    break;
                default:
                    logger.error("值异常");
            }

        }
    }

    /**
     * 创建单元格
     *
     * @param row   行
     * @param style 样式
     * @param index 索引
     * @param text  文本
     */
    private void createCell(XSSFRow row, XSSFCellStyle style, int index, String text, int indentNumber) {
        XSSFCell cell = row.createCell(index);

        StringBuilder sb = new StringBuilder();
        if (index != 2) {
            for (int i = 0; i < indentNumber; i++) {
                sb.append("   "); // 每两级之间差三个空格
            }
        }

        //如果公式为空，显示空字符串，不显示NULL字符
        text = text == null ? "" : text;

        sb.append(text);
        cell.setCellValue(sb.toString());

        cell.setCellStyle(style);
    }
}
